using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using DTO;

namespace DAO
{
    public class NguoiDungDAO
    {
        public void DangNhap(String User, String Pass, ref int Quyen)
        {
            try
            {
                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_DangNhap";

                ArrayList arr = new ArrayList();
                SqlParameter para = new SqlParameter("@User", User);
                arr.Add(para);

                para = new SqlParameter("@Pass", Pass);
                arr.Add(para);

                para = new SqlParameter("@Quyen", Quyen);
                para.Direction = ParameterDirection.Output;
                arr.Add(para);

                for (int i = 0; i < arr.Count; i++)
                    cmd.Parameters.Add(arr[i]);
                cmd.ExecuteNonQuery();
                Quyen = Convert.ToInt32(cmd.Parameters["@Quyen"].Value.ToString());

                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private NguoiDungDTO[] ConvertDTtoArr(DataTable dt)
        {
            int n = dt.Rows.Count;
            NguoiDungDTO[] ND = new NguoiDungDTO[n];
            for (int i = 0; i < n; i++)
            {
                ND[i] = new NguoiDungDTO();
                ND[i].MaUser = Convert.ToInt32(dt.Rows[i]["MaUser"].ToString());
                ND[i].Username = dt.Rows[i]["Username"].ToString();
                ND[i].Password = dt.Rows[i]["Password"].ToString();
                ND[i].Quyen = Convert.ToInt32(dt.Rows[i]["Quyen"].ToString());
                ND[i].HoTen = dt.Rows[i]["HoTen"].ToString();
                ND[i].CMND = dt.Rows[i]["CMND"].ToString();
                ND[i].DienThoai = dt.Rows[i]["DienThoai"].ToString();
                ND[i].Email = dt.Rows[i]["Email"].ToString();
                ND[i].DiaChi = dt.Rows[i]["DiaChi"].ToString();
            }
            return ND;
        }
        public DataTable LayDanhSachND()
        {
            //NguoiDungDTO[] ND = new NguoiDungDTO[100];
            DataTable dt = new DataTable();
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_layDSNguoiDung";
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dt;
        }
        public void ThemNguoiDung(string Username, string Password, int Quyen, string HoTen, string CMND, string DienThoai, string Email, string DiaChi)
        {
            NguoiDungDTO ndDTO = new NguoiDungDTO();
            ndDTO.Username = Username;
            ndDTO.Password = Password;
            ndDTO.Quyen = Quyen;
            ndDTO.HoTen = HoTen;
            ndDTO.CMND = CMND;
            ndDTO.DienThoai = DienThoai;
            ndDTO.Email = Email;
            ndDTO.DiaChi = DiaChi;
            ThemNguoiDung(ndDTO);
        }

        private void ThemNguoiDung(NguoiDungDTO nd)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemNguoiDung";

                cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Password", SqlDbType.NChar);
                cmd.Parameters.Add("@Quyen", SqlDbType.NVarChar);
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar);
                cmd.Parameters.Add("@CMND", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);

                cmd.Parameters["@Username"].Value = nd.Username;
                cmd.Parameters["@Password"].Value = nd.Password;
                cmd.Parameters["@Quyen"].Value = nd.Quyen;
                cmd.Parameters["@HoTen"].Value = nd.HoTen;
                cmd.Parameters["@CMND"].Value = nd.CMND;
                cmd.Parameters["@DienThoai"].Value = nd.DienThoai;
                cmd.Parameters["@Email"].Value = nd.Email;
                cmd.Parameters["@DiaChi"].Value = nd.DiaChi;

                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        public NguoiDungDTO[] TimKiemNguoiDung(int MaND)
        {
            NguoiDungDTO[] nd = new NguoiDungDTO[100];
            try
            {
                DataTable dt = new DataTable();
                string strMaND = " 1 = 1 ";
                string strMaTT = " 1 = 1 ";
                if (MaND != 0)
                {
                    strMaND = " ND.MaUser = " + MaND;
                    strMaTT = " TT.MaUser = " + MaND;
                }
                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();
                string strSQL = "SELECT * FROM NguoiDung ND,ThongTinUser TT WHERE ";
                strSQL += strMaND + " AND ";
                strSQL += strMaTT;
                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                nd = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return nd;
        }
        public NguoiDungDTO[] TimKiemNangCao(NguoiDungDTO kt)
        {
            NguoiDungDTO[] arr = new NguoiDungDTO[100];
            try
            {
                DataTable dt = new DataTable();
                string strUsername = " 1 = 1 ";
                string strHoTen = " 1 = 1 ";
                string strDienThoai = " 1 = 1 ";
                string strDiaChi = " 1 = 1 ";
                string strQuyen = " 1 = 1 ";
                string strMaTT = " 1 = 1 ";                

                if (string.Compare(kt.HoTen, "") != 0)
                    strHoTen = " TT.HoTen like N'%" + kt.HoTen + "%'";
                if (string.Compare(kt.Username, "") != 0)
                    strUsername = " ND.Username like N'%" + kt.Username + "%'";
                if (string.Compare(kt.DienThoai, "") != 0)
                    strDienThoai = " TT.DienThoai like N'%" + kt.DienThoai + "%'";
                if (string.Compare(kt.DiaChi, "") != 0)
                    strDiaChi = " TT.DiaChi like N'%" + kt.DiaChi + "%'";
                if (kt.Quyen != 0)
                    strQuyen = " ND.Quyen = " + kt.Quyen;
                strMaTT = "TT.MaUser = ND.MaUser ";
                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();
                string strSQL = "SELECT * FROM NguoiDung ND,ThongTinUser TT WHERE ";
                strSQL += strHoTen + " AND ";
                strSQL += strUsername + " AND ";
                strSQL += strDienThoai + " AND ";
                strSQL += strDiaChi + " AND ";
                strSQL += strQuyen + " AND ";
                strSQL += strMaTT;
                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                arr = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return arr;
        }
    }
}
